Excel的匯入匯出是我目前工作上常有的工作項目之一，雖然系統數位化了，但是有些對外的工作還是得轉成檔案來傳送，在這個案子中，我打算讓後台的題目新增除了手動的一題一題輸入外，也提供可以使用Excel匯入的方式，而前台則是提供使用者可以把測驗結果給輸出做為自行追踪之用。
這是一套基於PhpSpreadsheet所開發的Laravel套件，提供快速方便的Excel檔匯入匯出功能，當然如果你需要更進階的功能，可以考慮搭配 PhpSpreadsheet 來使用，因為你在安裝 Laravel Excel時，會自動安裝 PhpSpreadsheet。
到Laravel Excel官網去把指令複製過來執行:
composer require maatwebsite/excel
在 Laravel 9 以上的版本如果出現錯誤，可能是相關的依賴套件有問題，按照官網指示來處理：
composer require psr/simple-cache:^2.0 maatwebsite/excel
根據官網說明，安裝時會自動註冊 ServiceProvider 及 aliases ，但也提供自己手動註冊。
最後，把設定檔建立起來，之後有些功能直接在設定上改就可以了:
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
之後要使用時，只要在 VS CODE中下EXCEL關鍵字，就會自動把引用路徑給補上，很方便
使用說明部份詳閱官網，不逐一介紹。
Laravel Excel 官網文件
不是裝完就馬上可以用了，通常提供這種匯入功能，很難做到百分之百智慧和自動，所以我們要先定義一下匯入檔案的一些規範，比如資料的欄位要如何呈現，選項又是如何分配，要不要有標題列之類。
我這邊定義檔案的內容要有以下的欄位，並且要有這些欄位名的標題列：
我們需要有一個表單來提供上傳，這邊會使用 Inertia 提供的功能來以ajax的方式傳送檔案到後端去。
建立相關路由 - routes\web.php
Route::get('/subjects/{bank_id}/upload',[SubjectController::class,'upload'])
->name('subjects.upload');
Route::post('/subjects/import',[SubjectController::class,'import'])
->name('subjects.import');
在題目列表增加上傳的按鈕 - resources\js\Pages\Backstage\Subjects.vue
<Link :href="route('subjects.upload',bank.id)"
class="inline-block py-2 px-3 border rounded-xl
bg-orange-600 text-orange-50 my-4 mx-2" >
上傳題目
</Link>
準備表單頁面
<script setup>
import AuthenticatedLayout from "@/Layouts/AuthenticatedLayout.vue";
import { Head, Link, useForm } from "@inertiajs/inertia-vue3";
const props = defineProps({
header: { type: String, default: "上傳題目" },
button: { type: String, default: "上傳" },
bank:Object,
form: {
type: Object,
default: {
bankId: 0,
upload:''
},
},
});
const form = useForm(props.form);
form.bankId=props.bank.id
const submit = () => {
form.post(route("subjects.import"));
};
</script>
<template>
<Head title="管理中心" />
<AuthenticatedLayout>
<template #header>
<h2 class="font-semibold text-xl text-gray-800 leading-tight">管理中心</h2>
</template>
<div class="w-[calc(100%_-_12rem)] p-4">
<h2 class="text-xl font-bold">{{ header }}</h2>
<div>
<div>
<label>題目上傳：</label>
<input type="file" @input="form.upload = $event.target.files[0]" />
</div>
<button @click="submit" class="my-2 border px-6 py-2 rounded-xl shadow">
{{ button }}
</button>
</div>
</div>
</AuthenticatedLayout>
</template>
準備好Controller方法 - app\Http\Controllers\SubjectController.php
function upload(Request $request)
{
$bank=$this->subject->getBank($bank_id);
return Inertia::render('Backstage/UploadSubjects',
['bank'=>$bank]);
}
function import(Request $request)
{
dd($request->file());
}
簡單測試一下檔案有沒有傳到後端去
Laravel Excel會在Laravel中另外建立 Export 及 Import 兩個資料夾，Excel檔案解析的相關動作都在這裏執行，然後再返回給 Controller 或是 Model
建立Import
php artisan make:import SubjectImport
如果檔案有照規範的欄位製作，那麼按照官網的快速匯入範例，一下子就可以把資料都存入到資料表中，但是在我的經驗中，很多時候是要取得匯入結果的一些訊息來回報給前端，或是另外做一頁預覽每個要匯入的項目，讓使用者可以先做編修或調整再存入資料表。
所以大多時候，Import 的功能就只是幫助解析檔案，然後把資料轉成Collection丟回Controller中進行下一步的處理；
不過，我後來發現其實在 Import 中有很多事可以做，在翻找原始碼後，我現在除了拿來解析檔案之外，有些匯入的轉換工作也會在 Import 中就處理掉，這樣就可以讓進到Controller中的資料和使用新增功能傳來的表單資料格式是一致的，這也讓先前在Service中的新增題目功能是可以延用的，不用再為匯入另外寫一個新增題目的函式。
app\Imports\SubjectImport.php
namespace App\Imports;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection; //指定檔案使用Collection匯入
use Maatwebsite\Excel\Concerns\WithMapping; //把欄位進行對應
use Maatwebsite\Excel\Concerns\WithHeadingRow; //指定檔案有標題列
use App\Models\Bank; //引入Bank Model
use Maatwebsite\Excel\Imports\HeadingRowFormatter; //指定檔案標題列的格式
/* 預設的WithHeadinRow時會自動對標題進行命名格式化的動作，
* 但這個動作不支援中文，會變成亂碼或消失，
* 所以要設定不對標題列做任何處理，中文欄位名才能正確匯入*/
HeadingRowFormatter::default('none');
class SubjectImport implements ToCollection,WithHeadingRow
{
/**
* @param Collection $collection
*/
public function collection(Collection $collection)
{ }
/* 這個map方法會比toCollection還先執行，
* 所以我們可以在這裏處理資料匯入的邏輯
* 調整成和表單新增一致的格式
* */
public function map($row):array
{
$bank=Bank::where('code',$row['題庫'])
->where('level',$row['級別'])
->first(); //取得題庫
$row['seq']=$row['題號'];
$row['subject']=$row['題目'];
$row['bankId']=$bank->id;
$row['multiple']=$row['複選'];
if($row['複選']){
//把複選題的字串拆成字元存入陣列
$ans=str_split($row['答案']);
}else{
//單選題的話把答案放入陣列
$ans=[$row['答案']];
}
//根據$ans陣列中有無選項來決定該項目是否是答案
$row['options']=[1=>['option'=>$row['選項1'],'ans'=>in_array(1,$ans)],
2=>['option'=>$row['選項2'],'ans'=>in_array(2,$ans)],
3=>['option'=>$row['選項3'],'ans'=>in_array(3,$ans)],
4=>['option'=>$row['選項4'],'ans'=>in_array(4,$ans)]
];
//從題庫撈出題組，再根據題組編號找到題組id來指定
$row['bankGroupId']=$bank->bankgroups
->where('seq',$row['題組'])
->first()
->id;
//移除原本的欄位，只回傳存入資料表需要的資料就好
unset($row['題號'], $row['答案'], $row['題目'], $row['選項1'],
$row['選項2'], $row['選項3'], $row['選項4'], $row['複選'],
$row['題庫'], $row['級別'], $row['題組'], $row['11'], );
return $row;
}
}
在Controller把檔案解析成Collection後傳給Service處理，主要是檢查資料格式及內容有沒有問題，沒問題的資料就存入到資料表。
要注意的是 Laravel Excel 匯入時是包含所有的分頁都一起匯入的，所以解析出來的Collection是一個陣列，陣列中的每個元素都是一張分頁：
app\Http\Controllers\SubjectController.php
function subjectsInBank($bank_id)
{
$subjects=$this->subject->subjectsInBank($bank_id);
$bank=$this->subject->getBank($bank_id);
$bankgroups=$this->subject->getGroups($bank_id);
return Inertia::render('Backstage/Subjects',
[
'subjects'=>$subjects,
'bankgroups'=>$bankgroups,
'bank'=>$bank,
'count'=>$subjects->count(),
//增加一個屬性來判斷上傳檔案的狀態
'uploaded'=>(session('uploaded'))?session('uploaded'):false,
]);
}
function import(Request $request)
{
$subjects=Excel::toCollection(new SubjectImport,$request->file('upload'));
$uploaded=$this->subject->import($subjects[0]); //把第一張分頁的資料傳入Service
return redirect()
->route('bank.subjects',$request->bankId)
->with('uploaded',$uploaded); //匯入處理完畢，回傳訊息給頁面
}
把匯入的資料逐筆寫入資料表中 - app\Services\SubjectService.php
function import($subjects)
{
$subjects->each(function($subject){
$this->store($subject->toArray());
});
//匯入成功的話，回傳一些訊息給Controller,失敗也是
return ['status'=>'success','rows'=>$subjects->count()];
}
在題目列表增加對上傳結果的訊息顯示 - resources\js\Pages\Backstage\Subjects.vue
<script setup>
const props = defineProps({ subjects: Array,
bankgroups:Array,
count: Number ,
bank:Object,
uploaded:Object || Boolean, //增加接收一個上傳狀態的屬性
errors:Object});
</script>
<template>
.....略
<div v-if="uploaded"
class="text-xl font-bold my-4 mx-auto w-1/2 text-center">
<span v-if="uploaded.status==='success'"
class="text-orange-700 bg-yellow-300 py-3 px-7 rounded-xl">
上傳題目成功！一共新增了{{ uploaded.rows }}筆題目
</span>
<span v-else class="text-red-900 bg-red-500 py-3 px-7 rounded-xl">
上傳有誤，請檢查檔案內容格式並重新上傳出
</span>
</div>
.....略
</template>
善用套件提供的功能，的確可以節省不少自幹的時間，不過一開始也是花了不少時間在 Laravel Excel 和 PhpSpreadsheet 的官方文件及原始碼中穿梭，因為有些實際上想做到的功能官方文件是沒有提到的，甚至是 stackoverflow上 也沒有。
這裏留了個問題沒處理，當初新增單一題目時，我們並沒有對複選題進行處理，全部都當成單選題在存，而且在設定單選題時，就算你勾多個答案也是可以新增成功的，在測驗時也是全部當成單選題再選題，這邊匯入時我們卻有特別對複選題的項目做出處理；所以之後要再找個時間把單複選題的問題處理一下，現在只是針對匯入題目這功能先做一下確認。
iThome鐵人賽
看影片追技術